﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data.SqlClient;
using System.Data;
using System.Collections;
using System.Drawing;
using System.Drawing.Drawing2D;
using System.Transactions;
using DataAccess;
using Common;

#region VƯƠNG TRÍ LỢI
namespace BusinessLogic
{
   public class PhieuChi : MarshalByRefObject
    {
       static void LuuViet(string hanhDong, string maNhanVien, string noiDung, string tenDangNhap, string thoiGianThucHien)
       {
           try
           {
               Entities.LuuVietHeThong lv = new Entities.LuuVietHeThong();
               lv.HanhDong = hanhDong;
               lv.MaNhanVien = maNhanVien;
               lv.NoiDung = noiDung;
               lv.TenDangNhap = tenDangNhap;
               lv.ThoiGianThucHien = thoiGianThucHien;
               new LuuVietHeThong().LuuViet(lv);
           }
           catch
           {
               return;
           }
       }

       private static SqlCommand cmd;
       private static SqlDataReader dr;
       // private Entities.PhieuChi phieuchi;
       private static SqlConnection cn;

        /// <summary>
        /// select
        /// </summary>
        /// <param name="hdbh1"></param>
        /// <returns></returns>
       public static Entities.PhieuChi[] Select()
        {
            try
            {
                string sql = Common.Constants.PhieuChi.SelectPhieuChi;
                
                cn = DataAccess.Connection.OpenConnection();
                cmd = new SqlCommand(sql, cn);
                dr = cmd.ExecuteReader(CommandBehavior.CloseConnection);

                ArrayList arr = new ArrayList();
                while (dr.Read())
                {
                    Entities.PhieuChi row = new Entities.PhieuChi();
                    row.PhieuChiID = int.Parse(dr[Common.Constants.PhieuChi.PhieuChiID].ToString());
                    row.MaPhieuChi = dr[Common.Constants.PhieuChi.MaPhieuChi].ToString();
                    row.NgayLap =DateTime.Parse(dr[Common.Constants.PhieuChi.NgayLap].ToString());
                    row.MaDoiTuong = dr[Common.Constants.PhieuChi.MaDoiTuong].ToString();
                    row.KhoanMuc = dr[Common.Constants.PhieuChi.KhoanMuc].ToString();
                    row.DoiTuong = dr[Common.Constants.PhieuChi.DoiTuong].ToString();
                    row.TenDoiTuong = dr[Common.Constants.PhieuChi.TenDoiTuong].ToString();
                    row.DiaChi = dr[Common.Constants.PhieuChi.DiaChi].ToString();
                    row.MaSoThue = dr[Common.Constants.PhieuChi.MaSoThue].ToString();
                    row.NguoiNhanTien = dr[Common.Constants.PhieuChi.NguoiNhanTien].ToString();
                    row.MaTKNganHang = dr[Common.Constants.PhieuChi.MaTKNganHang].ToString();
                    row.CoTaiKhoan = dr[Common.Constants.PhieuChi.CoTaiKhoan].ToString();
                    row.NoTaiKhoan = dr[Common.Constants.PhieuChi.NoTaiKhoan].ToString();
                    row.TongTienThanhToan = dr[Common.Constants.PhieuChi.TongTienThanhToan].ToString();
                    row.MaKho = dr[Common.Constants.PhieuChi.MaKho].ToString();
                    row.GhiChu = dr[Common.Constants.PhieuChi.GhiChu].ToString();
                    row.Deleted = bool.Parse(dr[Common.Constants.PhieuChi.Deleted].ToString());
                    arr.Add(row);
                }
                int n = arr.Count;
                if (n == 0) return null;

                Entities.PhieuChi[] arrC = new Entities.PhieuChi[n];
                for (int i = 0; i < n; i++)
                {
                    arrC[i] = (Entities.PhieuChi)arr[i];
                }

                //Giai phong bo nho
                arr = null;
                cmd.Connection.Dispose();
                cn.Close();
                cn = null;
                
                return arrC;
            }
            catch { return new Entities.PhieuChi[0]; }
        }

       /// <summary>
       /// select theo ma dia diem
       /// </summary>
       /// <param name="hdbh1"></param>
       /// <returns></returns>
       public static Entities.PhieuChi[] SelectTheoDiaDiem(string MaKho)
       {
           try
           {
               string sql = Common.Constants.PhieuChi.SelectPhieuChiTheoDiaDiem;
               cn = DataAccess.Connection.OpenConnection();
               cmd = new SqlCommand(sql, cn);
               cmd.Parameters.Add(Common.Constants.PhieuChi.MaKho, SqlDbType.VarChar, 50).Value = MaKho;
               dr = cmd.ExecuteReader(CommandBehavior.CloseConnection);

               ArrayList arr = new ArrayList();
               while (dr.Read())
               {
                   Entities.PhieuChi row = new Entities.PhieuChi();
                   row.PhieuChiID = int.Parse(dr[Common.Constants.PhieuChi.PhieuChiID].ToString());
                   row.MaPhieuChi = dr[Common.Constants.PhieuChi.MaPhieuChi].ToString();
                   row.NgayLap = DateTime.Parse(dr[Common.Constants.PhieuChi.NgayLap].ToString());
                   row.MaDoiTuong = dr[Common.Constants.PhieuChi.MaDoiTuong].ToString();
                   row.KhoanMuc = dr[Common.Constants.PhieuChi.KhoanMuc].ToString();
                   row.DoiTuong = dr[Common.Constants.PhieuChi.DoiTuong].ToString();
                   row.TenDoiTuong = dr[Common.Constants.PhieuChi.TenDoiTuong].ToString();
                   row.DiaChi = dr[Common.Constants.PhieuChi.DiaChi].ToString();
                   row.MaSoThue = dr[Common.Constants.PhieuChi.MaSoThue].ToString();
                   row.NguoiNhanTien = dr[Common.Constants.PhieuChi.NguoiNhanTien].ToString();
                   row.MaTKNganHang = dr[Common.Constants.PhieuChi.MaTKNganHang].ToString();
                   row.CoTaiKhoan = dr[Common.Constants.PhieuChi.CoTaiKhoan].ToString();
                   row.NoTaiKhoan = dr[Common.Constants.PhieuChi.NoTaiKhoan].ToString();
                   row.TongTienThanhToan = dr[Common.Constants.PhieuChi.TongTienThanhToan].ToString();
                   row.MaKho = dr[Common.Constants.PhieuChi.MaKho].ToString();
                   row.GhiChu = dr[Common.Constants.PhieuChi.GhiChu].ToString();
                   row.Deleted = bool.Parse(dr[Common.Constants.PhieuChi.Deleted].ToString());
                   arr.Add(row);
               }
               int n = arr.Count;
               if (n == 0) return null;

               Entities.PhieuChi[] arrC = new Entities.PhieuChi[n];
               for (int i = 0; i < n; i++)
               {
                   arrC[i] = (Entities.PhieuChi)arr[i];
               }

               //Giai phong bo nho
               arr = null;
               cmd.Connection.Dispose();
               cn.Close();
               cn = null;
               
               return arrC;
           }
           catch { return new Entities.PhieuChi[0]; }
       }

        /// <summary>
        /// insert
        /// </summary>
        /// <param name="hdbh1"></param>
        /// <returns></returns>
        public static bool InsertUpdate(Entities.PhieuChi phieuchi)
       {
           TransactionScope ts = null;
           try
            {
               using (ts = new TransactionScope(TransactionScopeOption.Required))
                {
                    string sql = Common.Constants.PhieuChi.InsertUpdatePhieuChi;
                    cn = DataAccess.Connection.OpenConnection();
                    cmd = new SqlCommand(sql, cn);

                    cmd.Parameters.Add(Common.Constants.PhieuChi.HanhDong, SqlDbType.NVarChar, 20).Value = phieuchi.HanhDong;
                    cmd.Parameters.Add(Common.Constants.PhieuChi.PhieuChiID, SqlDbType.Int).Value = phieuchi.PhieuChiID;
                    cmd.Parameters.Add(Common.Constants.PhieuChi.MaPhieuChi, SqlDbType.VarChar, 50).Value = phieuchi.MaPhieuChi;
                    cmd.Parameters.Add(Common.Constants.PhieuChi.NgayLap, SqlDbType.DateTime).Value = phieuchi.NgayLap;
                    cmd.Parameters.Add(Common.Constants.PhieuChi.MaDoiTuong, SqlDbType.VarChar, 50).Value = phieuchi.MaDoiTuong;
                    cmd.Parameters.Add(Common.Constants.PhieuChi.KhoanMuc, SqlDbType.NVarChar, 200).Value = phieuchi.KhoanMuc;
                    cmd.Parameters.Add(Common.Constants.PhieuChi.DoiTuong, SqlDbType.NVarChar, 20).Value = phieuchi.DoiTuong;
                    cmd.Parameters.Add(Common.Constants.PhieuChi.TenDoiTuong, SqlDbType.NVarChar, 20).Value = phieuchi.TenDoiTuong;

                    cmd.Parameters.Add(Common.Constants.PhieuChi.DiaChi, SqlDbType.NVarChar, 50).Value = phieuchi.DiaChi;
                    cmd.Parameters.Add(Common.Constants.PhieuChi.MaSoThue, SqlDbType.VarChar, 50).Value = phieuchi.MaSoThue;
                    cmd.Parameters.Add(Common.Constants.PhieuChi.NguoiNhanTien, SqlDbType.NVarChar, 200).Value = phieuchi.NguoiNhanTien;
                    cmd.Parameters.Add(Common.Constants.PhieuChi.MaTKNganHang, SqlDbType.NVarChar, 50).Value = phieuchi.MaTKNganHang;
                    cmd.Parameters.Add(Common.Constants.PhieuChi.CoTaiKhoan, SqlDbType.VarChar, 50).Value = phieuchi.CoTaiKhoan;
                    cmd.Parameters.Add(Common.Constants.PhieuChi.NoTaiKhoan, SqlDbType.VarChar, 50).Value = phieuchi.NoTaiKhoan;

                    cmd.Parameters.Add(Common.Constants.PhieuChi.TongTienThanhToan, SqlDbType.Float).Value = phieuchi.TongTienThanhToan;
                    cmd.Parameters.Add(Common.Constants.PhieuChi.MaKho, SqlDbType.VarChar, 50).Value = phieuchi.MaKho;
                    cmd.Parameters.Add(Common.Constants.PhieuChi.MaNhanVien, SqlDbType.VarChar, 50).Value = phieuchi.MaNhanVien;
                    cmd.Parameters.Add(Common.Constants.PhieuChi.GhiChu, SqlDbType.NVarChar, 200).Value = phieuchi.GhiChu;
                    cmd.Parameters.Add(Common.Constants.PhieuChi.Deleted, SqlDbType.Bit).Value = phieuchi.Deleted;
                    int i = cmd.ExecuteNonQuery();
                    bool kt = false;
                    if (i == 1)
                    {
                        kt = true;


                        if (phieuchi.HanhDong == Common.Constants.Insert)
                        {
                            LuuViet
                                (
                                "Thêm",
                                phieuchi.TaiKhoan.MaNhanVien,
                                "Thêm phiếu chi " + phieuchi.MaPhieuChi,
                                phieuchi.TaiKhoan.TenDangNhap,
                                DateTime.Now.ToShortDateString()
                                );
                        }
                        else
                        {
                            LuuViet
                                (
                                "Sửa",
                                phieuchi.TaiKhoan.MaNhanVien,
                                "Sửa phiếu chi " + phieuchi.MaPhieuChi,
                                phieuchi.TaiKhoan.TenDangNhap,
                                DateTime.Now.ToShortDateString()
                                );
                        }

                    }
                    else
                        kt = false;
                    cmd.Connection.Dispose();
                    cn.Close();
                    cn = null; 

                    ts.Complete();
                    return kt;
               }
           }
           catch (Exception ex)
           {
               ts.Dispose();
               string s = ex.Message;
               return false;
           }
        }

        /// <summary>
        /// Delete
        /// </summary>
        /// <param name="hdbh1"></param>
        /// <returns></returns>
        public static bool Delete(Entities.PhieuChi pcdel)
        {
            try
            {
                bool kt = false;
                string sql = Common.Constants.PhieuChi.DeletePhieuChi;
                cn = DataAccess.Connection.OpenConnection();
                cmd = new SqlCommand(sql, cn);
          
                cmd.Parameters.Add(Common.Constants.PhieuChi.HanhDong, SqlDbType.NVarChar, 20).Value = pcdel.HanhDong;
                cmd.Parameters.Add(Common.Constants.PhieuChi.PhieuChiID, SqlDbType.Int).Value = pcdel.PhieuChiID;
                int i = cmd.ExecuteNonQuery();
                if (i == 1)
                {
                    kt = true;

                    if (pcdel.HanhDong == Common.Constants.Insert)
                    {
                        LuuViet
                            (
                            "Xóa",
                            pcdel.TaiKhoan.MaNhanVien,
                            "Xóa phiếu chi " + pcdel.MaPhieuChi,
                            pcdel.TaiKhoan.TenDangNhap,
                            DateTime.Now.ToShortDateString()
                            );
                    }




                }
                else
                    kt = false;
                cmd.Connection.Dispose();
                cn.Close();
                cn = null;
                
                return kt;
            }
            catch (Exception ex)
            {
                string s = ex.Message;
                return false;
            }
        }

        /// <summary>
        /// Update du no
        /// </summary>
        /// <param name="hdbh1"></param>
        /// <returns></returns>
        public static bool UpdateDuNo(Entities.KhachHang khachhang)
        {
            TransactionScope ts = null;
            try
            {
                    using (ts = new TransactionScope(TransactionScopeOption.Required))
                    {
                        string sql = Common.Constants.PhieuChi.UpdateDuNoKhachHang;
                        cn = DataAccess.Connection.OpenConnection();
                        cmd = new SqlCommand(sql, cn);

                        cmd.Parameters.Add(Common.Constants.KhachHang.HanhDong, SqlDbType.NVarChar, 20).Value = khachhang.HanhDong;
                        cmd.Parameters.Add(Common.Constants.KhachHang.MaKhachHang, SqlDbType.VarChar,50).Value = khachhang.MaKhachHang;
                        cmd.Parameters.Add(Common.Constants.KhachHang.DuNo, SqlDbType.Float).Value = khachhang.DuNo;
                        int i = cmd.ExecuteNonQuery();
                        bool kt = false;
                        if (i == 1)
                            kt = true;
                        else
                            kt = false;
                        cmd.Connection.Dispose();
                        cn.Close();
                        cn = null;

                        ts.Complete();
                        return kt;
                }
            }
            catch (Exception ex)
            {
                ts.Dispose();
                string s = ex.Message;
                return false;
            }
        }

        public static bool UpdateDuNo(Entities.NhaCungCap nhacungcap)
        {
            TransactionScope ts = null;
            try
            {
                using (ts = new TransactionScope(TransactionScopeOption.Required))
                {
                    string sql = Common.Constants.PhieuChi.UpdateDuNoNCC;
                    cn = DataAccess.Connection.OpenConnection();
                    cmd = new SqlCommand(sql, cn);

                    cmd.Parameters.Add(Common.Constants.NhaCungCap.HanhDong, SqlDbType.NVarChar, 20).Value = nhacungcap.HanhDong;
                    cmd.Parameters.Add(Common.Constants.NhaCungCap.MaNhaCungCap, SqlDbType.VarChar, 50).Value = nhacungcap.MaNhaCungCap;
                    cmd.Parameters.Add(Common.Constants.NhaCungCap.NoHienThoi, SqlDbType.Float).Value = nhacungcap.NoHienThoi;
                    int i = cmd.ExecuteNonQuery();
                    bool kt = false;
                    if (i == 1)
                        kt = true;
                    else
                        kt = false;
                    cmd.Connection.Dispose();
                    cn.Close();
                    cn = null;

                    ts.Complete();
                    return kt;
                }
            }
            catch (Exception ex)
            {
                ts.Dispose();
                string s = ex.Message;
                return false;
            }
        }

        public static Entities.KhachHang[] Selectkh(string manhanvien)
        {
            try
            {
                string sql = Common.Constants.PhieuChi.SelectDuNoKhachHang;
                cn = DataAccess.Connection.OpenConnection();
                cmd = new SqlCommand(sql, cn);
                cmd.Parameters.Add(Common.Constants.KhachHang.MaKhachHang, SqlDbType.NVarChar, 50).Value = manhanvien;
                dr = cmd.ExecuteReader(CommandBehavior.CloseConnection);

                ArrayList arr = new ArrayList();
                while (dr.Read())
                {
                    Entities.KhachHang row = new Entities.KhachHang();
                    row.DuNo = dr[Common.Constants.KhachHang.DuNo].ToString();
                    arr.Add(row);
                }
                int n = arr.Count;
                if (n == 0) return null;

                Entities.KhachHang[] arrC = new Entities.KhachHang[n];
                for (int i = 0; i < n; i++)
                {
                    arrC[i] = (Entities.KhachHang)arr[i];
                }

                //Giai phong bo nho
                arr = null;
                cmd.Connection.Dispose();
                cn.Close();
                cn = null;
                
                return arrC;
            }
            catch { return new Entities.KhachHang[0]; }

        }

        public static Entities.NhaCungCap[] Selectncc(string mancc)
        {
            try
            {
                string sql = Common.Constants.PhieuChi.SelectDuNoNCC;
                cn = DataAccess.Connection.OpenConnection();
                cmd = new SqlCommand(sql, cn);
                cmd.Parameters.Add(Common.Constants.NhaCungCap.MaNhaCungCap, SqlDbType.NVarChar, 50).Value = mancc;
                dr = cmd.ExecuteReader(CommandBehavior.CloseConnection);

                ArrayList arr = new ArrayList();
                while (dr.Read())
                {
                    Entities.NhaCungCap row = new Entities.NhaCungCap();
                    row.NoHienThoi = dr[Common.Constants.NhaCungCap.NoHienThoi].ToString();
                    arr.Add(row);
                }
                int n = arr.Count;
                if (n == 0) return null;

                Entities.NhaCungCap[] arrC = new Entities.NhaCungCap[n];
                for (int i = 0; i < n; i++)
                {
                    arrC[i] = (Entities.NhaCungCap)arr[i];
                }

                //Giai phong bo nho
                arr = null;
                cmd.Connection.Dispose();
                cn.Close();
                cn = null;
                
                return arrC;
            }
            catch { return new Entities.NhaCungCap[0]; }

        }

       /// <summary>
       ///  select khoan muc  theo loai khoan muc thuc hi
       /// </summary>
       /// <param name="km"></param>
       /// <returns></returns>
        public static Entities.KMThuChi[] SelectKMChi(string LoaiKM)
        {
            Entities.KMThuChi[] list = null;
            try
            {
                ArrayList arr = new ArrayList();
                cn = DataAccess.Connection.OpenConnection();
                cmd = new SqlCommand(Common.Constants.PhieuChi.SelectKMChi, cn);
                cmd.Parameters.Add(Common.Constants.KMThuChi.LoaiKM,SqlDbType.Bit).Value =  LoaiKM;
                dr = cmd.ExecuteReader(CommandBehavior.CloseConnection);
                arr = new ArrayList();
                while (dr.Read())
                {
                    Entities.KMThuChi row = new Entities.KMThuChi();
                    row.ThuChiID = int.Parse(dr[Common.Constants.KMThuChi.ThuChiID].ToString());
                    row.MaKhoanMuc = dr[Common.Constants.KMThuChi.MaKhoanMuc].ToString();
                    row.TenKhoanMuc = dr[Common.Constants.KMThuChi.TenKhoanMuc].ToString();
                    row.LoaiKM = Boolean.Parse(dr[Common.Constants.KMThuChi.LoaiKM].ToString());
                    row.DoiTuong = dr[Common.Constants.KMThuChi.DoiTuong].ToString();
                    row.NoTK = dr[Common.Constants.KMThuChi.NoTK].ToString();
                    row.CoTK = dr[Common.Constants.KMThuChi.CoTK].ToString();
                    row.GhiChu = dr[Common.Constants.KMThuChi.GhiChu].ToString();
                    row.Deleted = Boolean.Parse(dr[Common.Constants.KMThuChi.Deleted].ToString());
                    arr.Add(row);
                }
                int n = arr.Count;
                if (n == 0) { list = null; }
                list = new Entities.KMThuChi[n];
                for (int i = 0; i < n; i++)
                {
                    list[i] = (Entities.KMThuChi)arr[i];
                }
            }
            catch (Exception ex)
            { string s = ex.Message.ToString(); list = null; }
            finally
            {
                cmd.Connection.Dispose();
                cn.Close();
                
            }
            return list;
        }

        // Selct nhà cung cấp
        public static Entities.NhaCungCap[] SelectNCC()
        {
            Entities.NhaCungCap[] list = null;
            try
            {

                string sql = Common.Constants.PhieuChi.SelectNhaCungCap;

                cn = DataAccess.Connection.OpenConnection();
                cmd = new SqlCommand(sql, cn);
                dr = cmd.ExecuteReader(CommandBehavior.CloseConnection);
                ArrayList arr = new ArrayList();

                while (dr.Read())
                {
                    Entities.NhaCungCap nhacungcap = new Entities.NhaCungCap();
                    nhacungcap.TenNhaCungCap = dr[Common.Constants.NhaCungCap.TenNhaCungCap].ToString();
                    nhacungcap.DiaChi = dr[Common.Constants.NhaCungCap.DiaChi].ToString();
                    nhacungcap.DienThoai = dr[Common.Constants.NhaCungCap.DienThoai].ToString();
                    nhacungcap.Email = dr[Common.Constants.NhaCungCap.Email].ToString();
                    nhacungcap.Website = dr[Common.Constants.NhaCungCap.Website].ToString();
                    nhacungcap.Fax = dr[Common.Constants.NhaCungCap.Fax].ToString();
                    nhacungcap.NguoiLienHe = dr[Common.Constants.NhaCungCap.NguoiLienHe].ToString();
                    nhacungcap.MaSoThue = dr[Common.Constants.NhaCungCap.MaSoThue].ToString();
                    nhacungcap.GhiChu = dr[Common.Constants.NhaCungCap.GhiChu].ToString();
                    nhacungcap.Deleted = bool.Parse(dr[Common.Constants.NhaCungCap.Deleted].ToString());
                    nhacungcap.MaNhaCungCap = dr[Common.Constants.NhaCungCap.MaNhaCungCap].ToString();
                    arr.Add(nhacungcap);
                }
                int n = arr.Count;
                if (n == 0)
                {
                    list = new Entities.NhaCungCap[0];
                }
                else
                {
                    list = new Entities.NhaCungCap[n];
                    for (int i = 0; i < n; i++)
                    {
                        list[i] = (Entities.NhaCungCap)arr[i];
                    }
                }
                return list;
            }
            catch (Exception ex)
            { string s = ex.Message.ToString(); return new Entities.NhaCungCap[0]; }
            finally
            {
                cmd.Connection.Dispose();
                cn.Close();
            }

        }

    }
}

#endregion